package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu-jiaHui on 2017/2/28.
 */
public class SignfaultRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<SignfaultDto> getSignfault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "select sf.*,rp.Province,rp.City,rp.Area,rp.RoadName,rp.StartRoad,rp.EndRoad,rp.Longitude,rp.Latitude,rs.RoadSignPic,o.orgName\n" +
                "from (((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId=sf.RoadSignId) JOIN tb_roadpole rp ON rp.RoadPoleId=rs.RoadPoleId))" +
                "JOIN tb_org o ON o.orgId=sf.orgId \n" +
                "where concat(rp.RoadName,rp.StartRoad,rp.EndRoad) like '%" + dtRequestDto.getSearch() + "%'";
        if (actorId == 3) {
            sql += " and sf.OrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and sf.SignFaultPeopleId=" + userId + "";
        }
        sql += " order by (case sf.CompanyCheck when 0 then 0 else 2 END),sf.SignFaultTime desc ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
        List<SignfaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;
    }

    public Integer getCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "from (((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId=sf.RoadSignId) JOIN tb_roadpole rp ON rp.RoadPoleId=rs.RoadPoleId))" +
                "JOIN tb_org o ON o.orgId=sf.orgId " +
                "where concat(rp.RoadName,rp.StartRoad,rp.EndRoad) like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and sf.OrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and sf.SignFaultPeopleId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getUnCount(Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "select signfaultId from tb_signfault where and s.mainCheck=0";
        if (actorId == 3) {
            sql += " and OrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and SignFaultPeopleId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        List<Integer> signfaultDtos = query.getResultList();
        return signfaultDtos.size();
    }

    public List<SignfaultDto> getSignfault4Audit(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "select sf.*,rp.Province,rp.City,rp.Area,rp.RoadName,rp.StartRoad,rp.EndRoad,rs.RoadSignPic,rp.Longitude,rp.Latitude,o.orgName" +
                " from tb_signfault sf , tb_roadsign rs ,tb_roadpole rp ,tb_org o  where o.orgId=sf.OrgId and rs.RoadSignId=sf.RoadSignId and rp.RoadPoleId=rs.RoadPoleId" +
                " and o.orgId like '%" + dtRequestDto.getSearch() + "%'  and sf.CompanyCheck=1  ";
        if (actorId == 7) {
            sql += " and sf.MainOrgId=" + orgId + "";
        } else {
            sql += "";
        }
        sql += "  AND sf.MainCheck IN (0,1,-1) \n" +
                " order by  find_in_set(sf.MainCheck ,'0,1,-1') , sf.SignFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
        List<SignfaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;

    }

    public Integer getFaultAuditCount(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "select count(*)  from tb_signfault sf , tb_roadsign rs ,tb_roadpole rp ,tb_org o  where o.orgId=sf.OrgId and rs.RoadSignId=sf.RoadSignId and rp.RoadPoleId=rs.RoadPoleId" +
                " and o.orgId like '%" + dtRequestDto.getSearch() + "%' and sf.CompanyCheck=1  ";
        if (actorId == 7) {
            sql += " and sf.MainOrgId=" + orgId + "";
        } else {
            sql += "";
        }
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getFaultUnAuditCount(Integer actorId, Integer userId) throws Exception {
        String sql = "select DISTINCT signfaultId  ";
        if (actorId == 7) {
            sql += "from (tb_signfault s join tb_user u ON u.OrgId = s.MainOrgId ) where s.CompanyCheck=1 and s.mainCheck=0";
            sql += " AND u.UserId=:userId";
        } else {
            sql += "from (tb_signfault s , tb_user u  ) where s.CompanyCheck=1 and s.mainCheck=0";
        }
        Query query = em.createNativeQuery(sql);
        if (actorId == 7)
            query.setParameter("userId", userId);
        List<Integer> signfaultDtos = query.getResultList();
        return signfaultDtos.size();
    }

    public List<RoadpoleDto> getlongLatitude4Map(Integer roadSignId) throws Exception {
        String sql = "select rp.Longitude,rp.Latitude from tb_roadpole rp ,tb_roadsign rs where  rs.RoadSignId=:roadSignId and rp.RoadPoleId=rs.RoadPoleId;";
        Query query = em.createNativeQuery(sql);
        query.setParameter("roadSignId", roadSignId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        List<RoadpoleDto> roadpoleDto = query.getResultList();
        return roadpoleDto;
    }


    public List<SignFaultReportDto> getUnauditedFault(Integer orgId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +
                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount\n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n" +
                "where ((sf.MainCheck=0 AND sf.CompanyCheck=1) OR sf.CompanyCheck=0) ";
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += "AND sf.orgId = " + orgId + " ";
        }
        sql += "ORDER BY SignFaultTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignFaultReportDto> getUnpassFault(Integer orgId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +
                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount\n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n" +
                "where (sf.mainCheck=-1 OR sf.companyCheck=-1) ";
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += "AND sf.orgId = " + orgId + " ";
        }
        sql += "ORDER BY SignFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignFaultReportDto> getPassFault(Integer orgId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +
                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount\n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n" +
                "where sf.mainCheck=1 ";
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += "AND sf.orgId = " + orgId + " ";
        }
        sql += "ORDER BY SignFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignFaultReportDto> getUnauditedFaultByUserId(Integer userId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT sf.SignFaultId AS signFaultId,rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +
                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.CompanyUserId AS companyUserId,sf.MainUserId AS mainUserId, \n" +
                "sf.SignFaultPeopleId AS signFaultPeopleId,sf.SignFaultCheckBack AS signFaultCheckBack,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount\n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n";
        if (actorIds.indexOf("1") >= 0)//系统管理员
            sql += " WHERE (sf.CompanyCheck =0) OR (sf.MainCheck =0 AND sf.CompanyCheck=1 )  ";
        else if (actorIds.indexOf("3") >= 0)//审核员
            sql += " JOIN tb_user u ON u.OrgId = sf.OrgId WHERE u.UserId =:userId AND sf.CompanyCheck=0 ";
        else if (actorIds.indexOf("7") >= 0)//主管审核员
            sql += "  JOIN tb_user u ON u.OrgId = sf.MainOrgId WHERE u.UserId =:userId AND sf.CompanyCheck=1 AND sf.MainCheck=0";
        sql += " ORDER BY SignFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        if (!(actorIds.indexOf("1") >= 0))
            query.setParameter("userId", userId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignFaultReportDto> getPassFaultByuserId(Integer userId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT sf.SignFaultId AS signFaultId,rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +

                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.CompanyUserId AS companyUserId,sf.MainUserId AS mainUserId, \n" +
                "sf.SignFaultPeopleId AS signFaultPeopleId,sf.SignFaultCheckBack AS signFaultCheckBack,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount\n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n";
        if (actorIds.indexOf("1") >= 0)
            sql += " WHERE  sf.MainCheck =1 AND sf.CompanyCheck=1  ";
        else if (actorIds.indexOf("3") >= 0)//审核员
            sql += " JOIN tb_user u ON u.OrgId = sf.OrgId WHERE u.UserId =:userId AND sf.CompanyCheck=1 ";
        else if (actorIds.indexOf("7") >= 0)//主管审核员
            sql += "  JOIN tb_user u ON u.OrgId = sf.MainOrgId WHERE u.UserId =:userId AND sf.CompanyCheck=1 AND sf.MainCheck=1";
        sql += " ORDER BY SignFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        if (!(actorIds.indexOf("1") >= 0))
            query.setParameter("userId", userId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignFaultReportDto> getUnpassFaultByuserId(Integer userId, String actorIds, Integer count, Integer size) throws Exception {
        String sql = "SELECT sf.SignFaultId AS signFaultId,rs.RoadSignId AS roadSignId,rp.Province AS province,rp.City AS city,rp.Area AS area,\n" +
                "rp.RoadName AS roadName,rp.StartRoad AS startRoad,rp.EndRoad AS endRoad,rp.Longitude AS longitude,\n" +
                "rp.Latitude AS latitude,sf.SignFaultType AS signFaultType,sf.SignFaultReason AS signFaultReason,\n" +
                "sf.SignFaultPic AS signFaultPic,sf.SignFaultLevel AS signFaultLevel,sf.SignFaultTime AS signFaultTime,\n" +
                "sf.MainCheck AS mainCheck,sf.CompanyCheck AS companyCheck,sf.CompanyUserId AS companyUserId,sf.MainUserId AS mainUserId, \n" +
                "sf.SignFaultPeopleId AS signFaultPeopleId,sf.SignFaultCheckBack AS signFaultCheckBack,sf.OrgId AS orgId,sf.MainOrgId AS mainOrgId,sf.RepairAmount AS repairAmount \n" +
                " FROM ((tb_signfault sf JOIN tb_roadsign rs ON rs.RoadSignId = sf.RoadSignId)\n" +
                "JOIN tb_roadpole rp ON rp.RoadPoleId = rs.RoadPoleId)\n";
        if (actorIds.indexOf("1") >= 0)
            sql += " WHERE ( sf.CompanyCheck =-1) OR (sf.MainCheck =-1 AND sf.CompanyCheck=1 ) ";
        else if (actorIds.indexOf("3") >= 0)//审核员
            sql += " JOIN tb_user u ON u.OrgId = sf.OrgId WHERE u.UserId =:userId AND sf.CompanyCheck=-1 ";
        else if (actorIds.indexOf("7") >= 0)//主管审核员
            sql += "  JOIN tb_user u ON u.OrgId = sf.MainOrgId WHERE u.UserId =:userId AND sf.CompanyCheck=1 AND sf.MainCheck=-1";
        sql += " ORDER BY SignFaultTime DESC ";
        Query query = em.createNativeQuery(sql);
        if (!(actorIds.indexOf("1") >= 0))
            query.setParameter("userId", userId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignFaultReportDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }


    public List<SignfaultDto> getCurrentFault4DT(DTRequestDto dtRequestDto, Integer actorId, String maintenancePeopleName) throws Exception {
        String sql = "SELECT\n" +
                "s.SignFaultId,\n" +
                "s.SignFaultLevel,\n" +
                "s.SignFaultType,\n" +
                "s.SignFaultReason,\n" +
                "s.SignFaultDescription,\n" +
                "s.SignFaultCheckBack,\n" +
                "m.MaintenanceCreateTime,\n" +
                "s.MainUserId,\n" +
                "s.CompanyUserId,\n" +
                "s.SignFaultPeopleId,\n" +
                "m.MaintenancePeopleName\n" +
                "FROM\n" +
                "tb_signfault AS s\n" +
                "INNER JOIN tb_maintenance AS m ON m.SignFaultId = s.SignFaultId\n" +
                "WHERE\n" +
                "m.MaintenanceStatus <> 2 ";
        if (actorId != 1)
            sql += "AND m.MaintenancePeopleName = '" + maintenancePeopleName + "' ";
        sql += "AND concat(s.SignFaultId, s.signFaultLevel) like '%" + dtRequestDto.getSearch() + "%' order by m.MaintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
//        if (actorId != 1)
//            query.setParameter("maintenancePeopleName", maintenancePeopleName);
        List<SignfaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;
    }

    public Integer getCurrentFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, String maintenancePeopleName) throws Exception {
        String sql = "SELECT s.SignFaultId FROM tb_signfault AS s INNER JOIN tb_maintenance AS m ON m.SignFaultId = s.SignFaultId\n" +
                "WHERE m.MaintenanceStatus <> 2 ";
        if (actorId != 1)
            sql += "AND m.MaintenancePeopleName = '" + maintenancePeopleName + "' ";
        sql += "AND concat(s.SignFaultId, s.signFaultLevel) like '%" + dtRequestDto.getSearch() + "%' order by m.MaintenanceCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
//        if (actorId != 1)
//            query.setParameter("maintenancePeopleName", maintenancePeopleName);
        List<Integer> signfaultDtos = query.getResultList();
        return signfaultDtos.size();
    }

    public List<SignfaultDto> getRejectedFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select * from tb_signfault where ";
        if (actorId == 7)
            sql += "MainUserId=" + userId + " and MainCheck=-1 ";
        if (actorId == 3)
            sql += "CompanyUserId=" + userId + " and CompanyCheck=-1 ";
        if (actorId == 1)
            sql += "MainCheck=-1 or CompanyCheck=-1 ";
        sql += "and concat(signFaultId) like '%" + dtRequestDto.getSearch() + "%' ";
        sql += " order by SignCheckTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
        List<SignfaultDto> signfaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signfaultDtos;
    }

    public Integer getRejectedFaultCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId) throws Exception {
        String sql = "select SignFaultId from tb_signfault where  ";
        if (actorId == 7)
            sql += "MainUserId=" + userId + " and MainCheck=-1 ";
        if (actorId == 3)
            sql += "CompanyUserId=" + userId + " and CompanyCheck=-1 ";
        if (actorId == 1)
            sql += "MainCheck=-1 or CompanyCheck=-1 ";
        sql += "and concat(signFaultId) like '%" + dtRequestDto.getSearch() + "%' ";
        Query query = em.createNativeQuery(sql);
        List<Integer> signfaultDtos = query.getResultList();
        return signfaultDtos.size();
    }

/*    public List<UserOrgDto4Notice> getUserInfo2ReportFaultByOrg(Integer orgId) throws Exception {
        String sql = "SELECT o.OrgId AS orgId,o.OrgName AS orgName,u.UserId AS userId,u.UserName AS userName \n" +
                " FROM (tb_user u JOIN tb_user_actor ua ON ua.UserId=u.UserId)JOIN tb_org o ON o.OrgId=u.OrgId\n" +
                " WHERE ua.ActorId=5 AND o.OrgId=:orgId";
        Query query = em.createNativeQuery(sql);
        query.setParameter("orgId", orgId);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(UserOrgDto4Notice.class));
        List<UserOrgDto4Notice> userOrgDto4Notices = query.getResultList();
        return userOrgDto4Notices;
    }*/

    public List<SignfaultDto> getCheckByRoadSignId(Integer roadSignId) throws Exception {
        String sql = "select companyCheck,mainCheck from tb_signfault where RoadSignId=" + roadSignId + " order by SignFaultTime desc LIMIT 1";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
        List<SignfaultDto> signfaultDto = query.getResultList();
        return signfaultDto;
    }

    public String getCheckBackBySignFaultId(Integer roadSignId) throws Exception {
        String sql = "select signFaultCheckBack from tb_signfault where RoadSignId=" + roadSignId + " order by SignFaultTime desc LIMIT 1";
        Query query = em.createNativeQuery(sql);
        return query.getSingleResult().toString();
    }

}













